

USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspWSProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspWSProcess]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Process web-services requests in XML format and 
	return response in XML format
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	10/08/2012
  -----------------------------------------------------------------------
  Description/Purpose:
		1. Save request data in dbo.tblWSLog table
		2. Read XML into variables
		3. Validate @szPopUpJoeSessionID
		4. Create new session when @szPopUpJoeSessionID is null or not valid
			4.1 Find out store by @szStoreName and @szStoreGUID
				Note - @szStoreGUID has higher priority
			4.2 When store was found, validate Site parameters using @szStoreIPAddress and @szStoreDomainName
				Note - @szStoreDomainName has higher priority
			4.3 Use PPJ StoreID when store / site is revoked or was not found
			4.4 Create new record in dbo.tblSessionList
		5. Record details to dbo.tblRequestHeader / dbo.tblShoppingList	tables
		5. For correct store /site validate is discounted coupon exists
		6. Create XML Response, record output into dbo.tblWSLog table 

  Set of processed tables with relationship:
  
  dbo.tblWSLog
		|
		| link by WSLogID column
		|
		|_ dbo.tblRequestHeader	
				|	|
				|	| link by RequestHeaderID column
				|	|
				|	|_ dbo.tblShoppingList
				|
				| link by SessionID column
				|
				|_ dbo.tblSessionList
				
  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 10/08/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------


declare @xIn [xml],
	@biUpdUser [bigint],
	@xOut [xml]

--    <UsedCoupon>SAVE5DOLLAR</UsedCoupon>


-- <?xml version="1.0" encoding="utf-8"?>
select @xIn = N'<n:WSRequest xmlns:n="http://tempuri.org/PopUpJoeRequest.xsd"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RequestType>1</RequestType>
  <eCommerceServer>
    <StoreName>eStore1.com</StoreName>
    <Domain>eStore1.com</Domain>
  </eCommerceServer>
  <LogParameters>
<!--    <PopUpJoeSessionID>30663E09-A406-4BF8-9C8A-460655AA61EF</PopUpJoeSessionID>  -->
    <StoreGUID>902A5E3D-C259-4B51-BF0C-321D37A1DCB4</StoreGUID> 
  </LogParameters>
  <Customer>
    <IPAddress>108.53.0.141</IPAddress>
    <LoginName/>
   </Customer>
   <OrderTotal>143.20</OrderTotal>
   <Items>
    <Item>
      <ProductName>Belkin, N750 Wireless N+ Router</ProductName>
      <ItemCategory></ItemCategory>
      <SKU>BELKINN750</SKU>
      <Quantity>1</Quantity>
      <Price>120.20</Price>
    </Item>
    <Item>
      <ProductName>Test2</ProductName>
      <ItemCategory></ItemCategory>
      <SKU>Test_SKU</SKU>
      <Quantity>10</Quantity>
      <Price>8.00</Price>
    </Item>   
    <Item>
      <ProductName>Networking Punch Tool</ProductName>
      <ItemCategory/>
      <SKU>HD8762814</SKU>
      <Quantity>1</Quantity>
      <Price>15.00</Price>
    </Item>     
   </Items>
<!--   <UsedCoupon>SAVE5DOLLAR</UsedCoupon> -->
</n:WSRequest>'

exec [svc].[uspWSProcess] @xIn, @biUpdUser, @xOut output
select @xOut

*/

CREATE PROCEDURE [svc].[uspWSProcess]
	@xmlRequest [xml]
	,@biUpdUser [bigint] = null
	,@xmlResponse [xml] output 
AS
BEGIN

SET NOCOUNT ON;

set @biUpdUser = coalesce(@biUpdUser,0)

declare @biWSLogID [bigint],
	@iRequestType [int],
	@iCurrentRequestType [int],
	@biStoreID [bigint],
	@szStoreName [nvarchar] (255),
	@szStoreGUID [nvarchar] (255),
	@iSessionExpiration [int],
	@biStoreIPListID [bigint],
	@szStoreIPAddress [varchar] (64),
	@szStoreDomainName [nvarchar] (128),
	@iTimerValue1 [int],
	@iTimerValue2 [int],
	@nOrderTotal [decimal] (7,2),
	@szCustomerIPAddress [varchar] (64),
	@szCustomerLoginName [nvarchar] (128),
	@szFirstTimeVisitor [varchar] (2),
	@szFirstTimeBuyer [varchar] (2),
	@szLoggedIn [varchar] (2),
	@biSessionID [bigint],
	@szPopUpJoeSessionID [nvarchar] (256),
	@bStoreRevokeStatus [bit],
	@bStoreDomainRevokeStatus [bit],
	@bOrderSubmitFlag [bit],
	@dtDateOrderSubmit [datetime],
	@bPopUpFlag [bit],
	@dtDatePopUp [datetime],
	@biRequestHeaderID [bigint],
	@biCouponID [bigint],
	@szeCommCouponID [nvarchar] (64),
	@szMsgToPopUp  [nvarchar] (max),
	@biPPJStoreID [bigint],
	@iPPJSessionExpiration [int],
--	@xmlResponse [xml],
	@bUpdateCouponInfo [bit]

-- <?xml version="1.0" encoding="utf-8"?>
select @xmlResponse = 
'<n:WSResponse xmlns:n="http://tempuri.org/PopUpJoeResponse.xsd"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ResponseType>1</ResponseType>
  <PopUpJoeSessionID>[PPJS]</PopUpJoeSessionID>  
  <Timer>
    <ActivateTimer>N</ActivateTimer>
    <TimerDelay>0</TimerDelay>
	<ShowPopUpDuration>0</ShowPopUpDuration>
  </Timer>
  <Coupon>
    <Value>x</Value>
    <Message>x</Message>
  </Coupon>
  <Result>
    <ResultType>Success</ResultType>
  </Result>
</n:WSResponse>'
	,@iTimerValue1 = 0
	,@iTimerValue2 = 0
	,@bUpdateCouponInfo = 1
	
select @biPPJStoreID = StoreID, 
	@iPPJSessionExpiration = SessionExpiration
	from dbo.tblStore (nolock) 
	where StoreLevelID = 0 	 

-- 1. Insert data in WSLog table
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION XML_Request

	insert into dbo.tblWSLog (Request,DateRequest)
		values (@xmlRequest, GETDATE())
		
	set @biWSLogID = SCOPE_IDENTITY()	

COMMIT TRANSACTION XML_Request

-- 2. Start analysis of XML content

SELECT @iRequestType=@xmlRequest.value('(//RequestType)[1]','int'),

-- eCommerceServer
@szStoreName=@xmlRequest.value('(//StoreName)[1]','nvarchar(255)'),
@szStoreIPAddress=@xmlRequest.value('(//SiteIPAddress)[1]','nvarchar(64)'),
@szStoreDomainName=@xmlRequest.value('(//Domain)[1]','nvarchar(128)'),

-- LogParameters
@szStoreGUID=@xmlRequest.value('(//StoreGUID)[1]','nvarchar(255)'),
@szPopUpJoeSessionID=@xmlRequest.value('(//PopUpJoeSessionID)[1]','nvarchar(256)'),

-- Customer
@szCustomerIPAddress=@xmlRequest.value('(//IPAddress)[1]','varchar(64)'),
@szCustomerLoginName=@xmlRequest.value('(//LoginName)[1]','nvarchar(128)'),
@szFirstTimeVisitor=@xmlRequest.value('(//FirstTimeVisitor)[1]','varchar(2)'),
@szFirstTimeBuyer=@xmlRequest.value('(//FirstTimeBuyer)[1]','varchar(2)'),
@szLoggedIn=@xmlRequest.value('(//LoggedIn)[1]','varchar(2)'),

-- OrderTotal
@nOrderTotal=@xmlRequest.value('(//OrderTotal)[1]','decimal(7,2)'),

-- UsedCoupon Info
@szeCommCouponID = @xmlRequest.value('(//UsedCoupon)[1]','nvarchar(64)')


if coalesce(@szPopUpJoeSessionID,'')<>''
 begin
	select @biSessionID = SessionID,
		@iCurrentRequestType = RequestType,
		@bOrderSubmitFlag = OrderSubmitFlag,
		@dtDateOrderSubmit = DateOrderSubmit,
		@bPopUpFlag = PopUpFlag,
		@dtDatePopUp = DatePopUp
		from dbo.tblSessionList (nolock) 
		where PopUpJoeSessionID = @szPopUpJoeSessionID
			and Expiration > GETDATE()
 end

-- when record is not exists or already expired 
-- need to create new dbo.tblSessionList record
-- Store / Domain info should be validated before that
if coalesce(@biSessionID,0)= 0
 begin

-- Validate Store/Domain Info
	if not (coalesce(@szStoreName,'')='' and coalesce(@szStoreGUID,'')='')
	 begin
	 
		if coalesce(@szStoreGUID,'')<>''
		 begin
			select @biStoreID = StoreID, 
				@iSessionExpiration = SessionExpiration,
				@bStoreRevokeStatus = RevokeStatus
				from dbo.tblStore (nolock)
				where StoreGUID = @szStoreGUID
		 end
		 
		if  @biStoreID is null and coalesce(@szStoreName,'')<>''
		 begin
			select @biStoreID = StoreID, 
				@iSessionExpiration = SessionExpiration,
				@bStoreRevokeStatus = RevokeStatus
				from dbo.tblStore (nolock)
				where StoreName = @szStoreName
		 end
		 
	 end

	if @biStoreID is not null
	 begin

		if not (coalesce(@szStoreIPAddress,'')='' and coalesce(@szStoreDomainName,'')='')
		 begin

			if coalesce(@szStoreDomainName,'')<>''
			 begin
			 
				select @bStoreDomainRevokeStatus = d.RevokeStatus,
					@biStoreIPListID = d.StoreIPListID
					,@iTimerValue1 = d.TimerValue1
					,@iTimerValue2 = d.TimerValue2
					from dbo.tblStoreIPList d (nolock) 
					where d.StoreID = @biStoreID
					and d.DomainName = @szStoreDomainName
			 
			 end
			 
			if @biStoreIPListID is null and coalesce(@szStoreIPAddress,'')<>''
			 begin
			 
				select @bStoreDomainRevokeStatus = d.RevokeStatus,
					@biStoreIPListID = d.StoreIPListID
					,@iTimerValue1 = d.TimerValue1
					,@iTimerValue2 = d.TimerValue2
					from dbo.tblStoreIPList d (nolock) 
					where d.StoreID = @biStoreID
					and d.IPAddress = @szStoreIPAddress
			 
			 end 
		 
		 end
	 
	 end
	else
	 begin

		if  not (coalesce(@szStoreIPAddress,'')='' and coalesce(@szStoreDomainName,'')='')
		 begin

			if coalesce(@szStoreDomainName,'')<>''
			 begin

				;with t0 as (
					select s.StoreID, 
						s.SessionExpiration,
						s.RevokeStatus as StoreRevokeStatus,
						d.RevokeStatus as DomainRevokeStatus,
						d.StoreIPListID,
						d.TimerValue1,
						d.TimerValue2,
						ROW_NUMBER() OVER(PARTITION BY s.StoreID ORDER BY s.RevokeStatus, d.RevokeStatus) as RNumber 
						from dbo.tblStore s (nolock)
						inner join dbo.tblStoreIPList d (nolock) on d.StoreID = s.StoreID
						where d.DomainName = @szStoreDomainName
				)
				select @biStoreID = StoreID, 
					@iSessionExpiration = SessionExpiration,
					@bStoreRevokeStatus = StoreRevokeStatus,
					@bStoreDomainRevokeStatus = DomainRevokeStatus,
					@biStoreIPListID = StoreIPListID
					,@iTimerValue1 = TimerValue1
					,@iTimerValue2 = TimerValue2
					from t0
					where RNumber = 1 

			 end
			 
			if @biStoreID is null and coalesce(@szStoreIPAddress,'')<>''
			 begin
				;with t0 as (
					select s.StoreID, 
						s.SessionExpiration,
						s.RevokeStatus as StoreRevokeStatus,
						d.RevokeStatus as DomainRevokeStatus,
						d.StoreIPListID,
						d.TimerValue1,
						d.TimerValue2,
						ROW_NUMBER() OVER(PARTITION BY s.StoreID ORDER BY s.RevokeStatus, d.RevokeStatus) as RNumber 
						from dbo.tblStore s (nolock)
						inner join dbo.tblStoreIPList d (nolock) on d.StoreID = s.StoreID
						where d.DomainName = @szStoreIPAddress
				)
				select @biStoreID = StoreID, 
					@iSessionExpiration = SessionExpiration,
					@bStoreRevokeStatus = StoreRevokeStatus,
					@bStoreDomainRevokeStatus = DomainRevokeStatus,
					@biStoreIPListID = StoreIPListID				
					,@iTimerValue1 = TimerValue1
					,@iTimerValue2 = TimerValue2
					from t0
					where RNumber = 1 
			 
			 end
			 		 
		 end
	 
	 end
	 
	if @biStoreID is not null
	 begin
	 -- validate revoke statuses on Domain and Store level
		if coalesce(@bStoreRevokeStatus,0) = 1 or
			coalesce(@bStoreDomainRevokeStatus,0) = 1
		 begin
			set @biStoreID = null		 
		 end	 
		else if coalesce(@szeCommCouponID,'')<>'' and 
			coalesce(@biStoreIPListID,0)<>0
		 begin
			select @biCouponID = CouponID 
				from dbo.tblCoupon (nolock)
				where StoreIPListID = @biStoreIPListID
				and eCommCouponID = @szeCommCouponID
		 end

	 end	
	 
	if @biStoreID is null
	 begin
-- cannot identify store, or store or store domain has status revoke	 
-- take PPJ ID for future using

		select @biStoreID = @biPPJStoreID,
			@iSessionExpiration = @iPPJSessionExpiration
			 
	 end

	if coalesce(@iSessionExpiration,0) <= 0
	 set @iSessionExpiration = 15*60 -- default duration session is 15 minutes

-- ready to insert record into dbo.tblSessionList
-- 2
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION XML_Request
	set @szPopUpJoeSessionID = NEWID()
	
	insert into dbo.tblSessionList (
		RequestType,
		PopUpJoeSessionID,
		Expiration,
		PopUpFlag,
		OrderSubmitFlag,
		UserCreated,
		DateCreated)
	values (@iRequestType,
		@szPopUpJoeSessionID,
		DATEADD(s,@iSessionExpiration,GETDATE()),
		0,0,
		@biUpdUser,
		getdate())
	
	set @biSessionID = SCOPE_IDENTITY()	

	COMMIT TRANSACTION XML_Request

 end

-- we recognized what store should be used
--	and have @biSessionID

select @bPopUpFlag = coalesce(@bPopUpFlag,0), 
	@bOrderSubmitFlag = coalesce(@bOrderSubmitFlag,0),
	@bStoreRevokeStatus = coalesce(@bStoreRevokeStatus,0),
	@bStoreDomainRevokeStatus = coalesce(@bStoreDomainRevokeStatus,0)

-- Update dbo.tblSessionList record by new statuses
if @iCurrentRequestType is not null and @iCurrentRequestType <> @iRequestType
 begin

	if @iCurrentRequestType = 1 and @iRequestType = 2
	 begin
		select @bPopUpFlag = 1,
			@dtDatePopUp = GETDATE()

		update dbo.tblSessionList set
			RequestType = @iRequestType,
			PopUpFlag = 1,
			DatePopUp = @dtDatePopUp,
			UserUpdated = @biUpdUser,
			DateUpdated = GETDATE()
			where SessionID = @biSessionID

	 end
	else if @iCurrentRequestType = 2 and @iRequestType = 3
	 begin

		select @bOrderSubmitFlag = 1,
			@dtDateOrderSubmit = GETDATE()

		update dbo.tblSessionList set
			RequestType = @iRequestType,
			OrderSubmitFlag = 1,
			DateOrderSubmit = @dtDateOrderSubmit,
			UserUpdated = @biUpdUser,
			DateUpdated = GETDATE()
			where SessionID = @biSessionID

	 end
 
 end

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION XML_Request
 
-- 3 can insert data into dbo.tblRequestHeader table
insert into dbo.tblRequestHeader (
	[WSLogID],
	[RequestType],
	[StoreID],
	[StoreName],
	[StoreGUID],
	[StoreDomainName],
	[StoreIPListID],
	[SessionID],
	[CustomerIPAddress],
	[CustomerLoginName],
	[FirstTimeVisitor],
	[FirstTimeBuyer],
	[LoggedIn],
	[OrderTotal],
	[eCommCouponID],
	[CouponID],
	[PopUpFlag],
	[DatePopUp],
	[OrderSubmitFlag],
	[DateOrderSubmit],
	[DateCreated],
	[UserCreated],
	[StoreRevokeStatus],
	[StoreDomainRevokeStatus],
	[PopUpJoeSessionID])
values (	 
	@biWSLogID,
	@iRequestType,
	@biStoreID,
	@szStoreName,
	@szStoreGUID,
	@szStoreDomainName,
	@biStoreIPListID,
	@biSessionID,
	@szCustomerIPAddress,
	@szCustomerLoginName,
	case when coalesce(@szFirstTimeVisitor,'N')='Y' then 1 else 0 end,
	case when coalesce(@szFirstTimeBuyer,'N')='Y' then 1 else 0 end,
	case when coalesce(@szLoggedIn,'N')='Y' then 1 else 0 end,
	@nOrderTotal,
	@szeCommCouponID,
	@biCouponID,
	@bPopUpFlag,
	@dtDatePopUp,
	@bOrderSubmitFlag,
	@dtDateOrderSubmit,
	getdate(),
	@biUpdUser,
	@bStoreRevokeStatus,
	@bStoreDomainRevokeStatus,
	@szPopUpJoeSessionID)

set @biRequestHeaderID = SCOPE_IDENTITY()  

insert into dbo.tblShoppingList (
	RequestHeaderID,
	SKU,
	ProductName,
	ItemCategory,
	Quantity,
	Price,
	DateCreated,
	UserCreated)
SELECT @biRequestHeaderID
	,T.c.value('SKU[1]','nvarchar(64)') as SKU
	,T.c.value('ProductName[1]','nvarchar(255)') as ProductName
	,T.c.value('ItemCategory[1]','nvarchar(255)') as ItemCategory
	,T.c.value('Quantity[1]','decimal(12,4)') as Quantity
	,T.c.value('Price[1]','decimal(7,2)') as Price
	,GETDATE()
	,@biUpdUser
	FROM @xmlRequest.nodes('//Items/Item') T(c)

COMMIT TRANSACTION XML_Request

SET @xmlResponse.modify('replace value of (//PopUpJoeSessionID[1]/text())[1]
  with sql:variable("@szPopUpJoeSessionID")')

if @iRequestType <> 1
	SET @xmlResponse.modify('replace value of (//ResponseType[1]/text())[1]
	  with sql:variable("@iRequestType")')

if @biStoreID <> @biPPJStoreID
 begin

	if @iRequestType = 1
		and @iTimerValue1 >= 0 
		and @iTimerValue2 >= 0
	 begin
print '-'
		select top 1 
			@szeCommCouponID = eCommCouponID,
			@szMsgToPopUp = MsgToPopUp,
			@biCouponID = CouponID 
			from dbo.tblCoupon (nolock)
			where StoreIPListID = @biStoreIPListID
			and RevokeStatus = 0
			and (ExpirationDate is NULL or 
				(ExpirationDate is not null and ExpirationDate > GETDATE()))
			and (LimitedNumber = 0 or (LimitedNumber = 1 and Uses > 0))
			and MinOrderAmt <= @nOrderTotal
			and (MaxOrderAmt = 0 or (MaxOrderAmt > 0 and MaxOrderAmt >= @nOrderTotal))
		
		if @@ROWCOUNT <> 0 and coalesce(@szeCommCouponID,'')<>''
		 begin

			--select @xmlResponse = REPLACE(@xmlResponse,'<ActivateTimer>N</ActivateTimer>',
			--	'<ActivateTimer>Y</ActivateTimer>')
			--select @xmlResponse = REPLACE(@xmlResponse,'<TimerDelay>0</TimerDelay>',
			--	'<TimerDelay>'+CAST(@iTimerValue1 as [varchar] (10))+'</TimerDelay>')
			--select @xmlResponse = REPLACE(@xmlResponse,'<ShowPopUpDuration>0</ShowPopUpDuration>',
			--	'<ShowPopUpDuration>'+CAST(@iTimerValue2 as [varchar] (10))+'</ShowPopUpDuration>')
			--select @xmlResponse = REPLACE(@xmlResponse,'<Value></Value>',
			--	'<Value>'+@szeCommCouponID+'</Value>')
			--select @xmlResponse = REPLACE(@xmlResponse,'<Message></Message>',
			--	'<Message>'+@szMsgToPopUp+'</Message>')
--|
print 1
			SET @xmlResponse.modify('replace value of (//ActivateTimer[1]/text())[1] with "Y"')

			SET @xmlResponse.modify('replace value of (//TimerDelay[1]/text())[1]
			  with sql:variable("@iTimerValue1")')

			SET @xmlResponse.modify('replace value of (//ShowPopUpDuration[1]/text())[1]
			  with sql:variable("@iTimerValue2")')

			SET @xmlResponse.modify('replace value of (//Value[1]/text())[1]
			  with sql:variable("@szeCommCouponID")')

			SET @xmlResponse.modify('replace value of (//Message[1]/text())[1]
			  with sql:variable("@szMsgToPopUp")')
--|
		
			set @bUpdateCouponInfo = 0
				
			update 	dbo.tblRequestHeader set 
				ActivateTimer = 'Y',
				TimerValue1 = @iTimerValue1,
				TimerValue2 = @iTimerValue2,
				eCommCouponID = @szeCommCouponID,
				CouponID = @biCouponID,
				UserUpdated = @biUpdUser,
				DateUpdated = GETDATE()
				where RequestHeaderID = @biRequestHeaderID
			
		 end -- if @@ROWCOUNT <> 0 and coalesce(@szeCommCouponID,'')<>''
	 end	 -- if @iRequestType = 1 and @iTimerValue1 >= 0 and @iTimerValue2 >= 0
--	else
--	 begin
--print 2
--		SET @xmlResponse.modify('replace value of (//Value[1]/text())[1] with ""')
--		SET @xmlResponse.modify('replace value of (//Message[1]/text())[1] with ""')
--	 end	 
 end		 -- if @biStoreID <> @biPPJStoreID
--else
-- begin
-- print 3
--	SET @xmlResponse.modify('replace value of (//Value[1]/text())[1] with ""')
--	SET @xmlResponse.modify('replace value of (//Message[1]/text())[1] with ""')
-- end 

if @bUpdateCouponInfo = 1
 begin
	SET @xmlResponse.modify('replace value of (//Value[1]/text())[1] with ""')
	SET @xmlResponse.modify('replace value of (//Message[1]/text())[1] with ""')
 end
 
update dbo.tblWSLog set Response = @xmlResponse,
	DateResponse = GETDATE()
	where WSLogID = @biWSLogID 

--select @xmlResponse

RETURN
END 
GO
--\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
--!! Need update CouponID in dbo.tblRequestHeader
-- need generate response and update tblWSLog
-- need to create proper Error handling always return proper XML !!!
